package cn.kgc.tangcco.tcbd1011.no13.vehicle.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.kgc.tangcco.tcbd1011.no13.base.dao.BaseDao;
import cn.kgc.tangcco.tcbd1011.no13.base.util.JDBCUtils;
import cn.kgc.tangcco.tcbd1011.no13.vehicle.dao.VehicleDao;
import cn.kgc.tangcco.tcbd1011.no13.vehicle.entity.Vehicle;
import cn.kgc.tangcco.tcbd1011.no13.vehicle.vo.VehicleVo;

public class VehicleDaoImpl extends BaseDao implements VehicleDao {
	private QueryRunner queryRunner;
	public VehicleDaoImpl() {
		queryRunner = new QueryRunner(JDBCUtils.getDataSource());
	}

	@Override//查询多条汽车信息
	public List<VehicleVo> queryAllVehicle(VehicleVo vehicle) {
		//联合查询使用
		List<Object> list = new ArrayList<>();
		//联合查询使用
		StringBuffer sb = new StringBuffer();
		//拼接sql前缀
		sb.append("SELECT  t.`typeName`,vd.`vehicleBrand`,vt.`vehicleTypeName`,vt.`seat`,v.* FROM vehicle v ,vehicletype vt,vehiclebrand vd,`type` t WHERE v.`vehicleTypeId`=vt.`vehicleTypeId` AND vt.`vehicleBrandId` = vd.`vehicleBrandId` AND vt.`typeId` = t.`typeId` ");
		
		//拼接sql后缀并转换为String类型
		String sql = sb.append(" ORDER BY v.vehicleId DESC").toString();
		try {
			//执行查询多条信息sql语句。填充？号
			return queryRunner.query(sql, new BeanListHandler<>(VehicleVo.class), list.toArray());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override//查询一条汽车信息
	public VehicleVo detailVehicle(int pk) {
		String sql = "SELECT  t.`typeName`,vd.`vehicleBrand`,vt.`vehicleTypeName`,vt.`seat`,v.* FROM vehicle v ,vehicletype vt,vehiclebrand vd,`type` t WHERE v.`vehicleTypeId`=vt.`vehicleTypeId` AND vt.`vehicleBrandId` = vd.`vehicleBrandId` AND vt.`typeId` = t.`typeId` AND v.`vehicleId` = ? ORDER BY v.vehicleId DESC";
		try {
			return this.queryRunner.query(sql, new BeanHandler<>(VehicleVo.class), pk);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override//查询总数
	public int queryCountVehicle() {
		String sql  ="SELECT  COUNT(*) FROM vehicle v ,vehicletype vt,vehiclebrand vd,`type` t WHERE v.`vehicleTypeId`=vt.`vehicleTypeId` AND vt.`vehicleBrandId` = vd.`vehicleBrandId` AND vt.`typeId` = t.`typeId`";
		try {
			return ((Long)this.queryRunner.query(sql, new ScalarHandler<>())).intValue();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override //新增一条汽车信息
	public int addVehicle(Vehicle vehicle) {
		return this.executeUpdate(queryRunner, "INSERT INTO vehicle  VALUES(DEFAULT,?,?,?,?)", vehicle.getVehicleTypeId(),vehicle.getPlateNumber(),vehicle.getDisplacement(),vehicle.getDate());
		
	}

	@Override //修改一条汽车信息
	public int updateVehicle(Vehicle vehicle) {
		return this.executeUpdate(queryRunner, "UPDATE vehicle SET vehicleTypeId=?,plateNumber=?,displacement=?,date=? WHERE vehicleId = ?",
				vehicle.getVehicleTypeId(),vehicle.getPlateNumber(),vehicle.getDisplacement(),vehicle.getDate(),vehicle.getVehicleId());
	}
	@Override//删除一条汽车信息
	public int deleteVehicle(int pk) {
		return this.executeUpdate(queryRunner, "DELETE FROM vehicle WHERE vehicleId = ?", pk);
	}

}
